<?php
class NoticeMsgInfo extends AppModel {
	var $name = "NoticeMsgInfo";
	var $useTable = "notice_msg_info";
	var $primaryKey = 'id';

	/**
	 * 告知メール内容を取得する
	 * @param unknown_type $noticeId
	 */
	function getNoticeMsgInfo($noticeId) {
		$sql = "SELECT 
					sr.id as srid,sr.status, 
					msg.id,msg.company_account_id,
					notice_thema.company_id, 	
					sr.received,  
					msg.subject, 
					replace(msg.contents,'%company%',cm.company_name) as contents, 
					'カモメ編集室' as name, 
					cm.company_name 
					,ac.role AS role 
				FROM notice_msg_reaction_info as sr 
				JOIN notice_msg_info as msg  
				ON sr.notice_id = msg.id  
				AND sr.deleted is null AND sr.member_deleted is null   
				JOIN company_account_mst as ac ON msg.company_account_id = ac.id 
				AND ac.deleted is null AND msg.deleted is null 	 		
			    join notice_thema_info as notice_thema
			     on notice_thema.thema_id = msg.thema_id				
				JOIN company_info as cm ON notice_thema.company_id = cm.id 
				AND cm.deleted is null
				WHERE sr.id =". $noticeId ;
		$msgRs = $this->query($sql);
		return $msgRs[0][0];
	}
	
}

?>